﻿using Kjs.Report.DB;
using Kjs.Report.IService;
using Kjs.Report.Model;
using System;
using System.Collections.Generic;
using System.Text;

namespace Kjs.Report.Service
{
    /// <summary>
    /// 促销商品
    /// </summary>
    public class PromotionGoodService : IPromotionGoodService
    {
        /// <summary>
        /// 按照商品名称分组统计销售数据明细
        /// </summary>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_PromotionGoodTitleGroup>> GoodNameGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	d.title,
	d.sell_price,
	b.real_price promotion_price,
	count(b.quantity) total_count,
	sum(b.real_price) total_amount
FROM
	orders a
INNER JOIN order_good b ON a.id = b.order_id
INNER JOIN good_promotion c ON b.good_id = c.good_id
INNER JOIN goods d ON b.good_id = d.id
INNER JOIN terminals e ON a.terminal_id = e.id
WHERE
a.`status` = 3
AND b.real_price < d.sell_price
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(@" group by 	d.title,

    d.sell_price,
    b.real_price  ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " total_count DESC ";

            return PagedHelper.PagedList<M_PromotionGoodTitleGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);

        }


        /// <summary>
        /// 按照时间分组统计销售数据明细
        /// </summary>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_PromotionTimeGroup>> TimeGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	DATE_FORMAT(a.add_time, '%Y-%m-%d') time,
	count(b.quantity) total_count,
	sum(b.real_price) total_amount
FROM
	orders a
INNER JOIN order_good b ON a.id = b.order_id
INNER JOIN good_promotion c ON b.good_id = c.good_id
INNER JOIN goods d ON b.good_id = d.id
INNER JOIN terminals e ON a.terminal_id = e.id
WHERE
	a.`status` = 3
AND b.real_price < d.sell_price
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(@" group by 	time ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " time DESC ";

            return PagedHelper.PagedList<M_PromotionTimeGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);

        }


        /// <summary>
        /// 按照时间及商品名称分组统计销售数据明细
        /// </summary>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="filedOrder">排序字段</param>
        /// <param name="orgIds">组织编号[多个用  , 隔开]</param>
        /// <param name="line_id">线路编号</param>
        /// <param name="point_id">点位编号</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public PagedList<IList<M_PromotionGoodTitleAndTimeGroup>> TitleAndTimeGroupList(int pageSize, int pageIndex, string filedOrder, string orgIds, int line_id, int point_id,
            DateTime? beginTime, DateTime? endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	DATE_FORMAT(a.add_time, '%Y-%m-%d') time,
	b.good_title,
	d.sell_price,
	c.promotion_price,
	count(b.quantity) total_count,
	sum(b.real_price) total_amount
FROM
	orders a
INNER JOIN order_good b ON a.id = b.order_id
INNER JOIN good_promotion c ON b.good_id = c.good_id
INNER JOIN goods d ON b.good_id = d.id
INNER JOIN terminals e ON a.terminal_id = e.id
WHERE
	a.`status` = 3
AND b.real_price < d.sell_price
");
            if (!string.IsNullOrWhiteSpace(orgIds))
                strSql.AppendFormat(" and a.org_id in ({0}) ", orgIds);

            if (line_id > 0)
                strSql.AppendFormat(" and e.line_id = {0} ", line_id);

            if (point_id > 0)
                strSql.AppendFormat(" and e.point_id = {0} ", point_id);

            if (beginTime != null)
                strSql.AppendFormat(" and a.add_time >= '{0}' ", beginTime);

            if (endTime != null)
                strSql.AppendFormat(" and a.add_time < '{0}' ", endTime);

            strSql.Append(@" GROUP BY
	time,
	b.good_title,
	d.sell_price,
	c.promotion_price ");

            if (string.IsNullOrWhiteSpace(filedOrder))
                filedOrder = " time DESC ";

            return PagedHelper.PagedList<M_PromotionGoodTitleAndTimeGroup>(strSql.ToString(), pageSize, pageIndex, filedOrder);

        }
    }
}
